import os
from os import system, name

import pymssql
from pymssql import OperationalError
from pymssql._mssql import MSSQLDatabaseException

# 学号
student_number = "201900800290"
# 系统名
sys_name = "BOOKS"
# 数据库名
db_name = sys_name + "_" + student_number
# 系统文件路径
path = "D:\\" + db_name + "\\DB\\"
# 数据库路径
db_file_path = path + db_name
# 日志名
log_name = db_name + "_log"
# 日志文件路径
log_file_path = path + log_name

'''
建表语句
'''
# 管理员表
admin = '''create table admin
(
    account  nvarchar(20) primary key,
    password nvarchar(20)
)'''

# 图书表
book = '''create table book
(
    book_id      nvarchar(20) primary key,
    name         nvarchar(20),
    author       nvarchar(20),
    publisher    nvarchar(20),
    publish_date date,
    price        float,
    summary      nvarchar(50),
    is_borrowed  bit default 0
)'''

# 读者表
reader = '''create table reader
(
    name     nvarchar(20) primary key,
    gender   tinyint,
    unit     nvarchar(40),
    tel      nvarchar(20),
    balance  float,
    password nvarchar(20)
)'''

# 角色表（教师、研究生、本科生、其他）
role = '''create table role
(
    name              nvarchar(20) primary key ,
    borrow_num_limit  int,
    borrow_date_limit int
)'''

# 读者-角色联系表
reader_role = '''create table reader_role
(
    reader_name nvarchar(20) primary key,
    role_name   nvarchar(20)
    foreign key (reader_name) references reader(name),
    foreign key (role_name) references role(name)
)'''

# 借阅表
borrow = '''create table borrow
(
    reader_name   nvarchar(20),
    book_id       nvarchar(20),
    borrow_date   datetime,
    return_date   datetime,
    is_overdue    bit, 
    overdue_money float,
    is_process    bit,
    primary key (reader_name, book_id),
    foreign key (reader_name) references reader(name),
    foreign key (book_id) references book(book_id)
)'''

# 建立索引
index_reader_name = '''create index idx_reader_name on reader(name asc)'''
index_book_id = '''create index idx_book_id on book(book_id asc)'''
index_role_name = '''create index idx_role_name on role(name asc )'''
index_reader_role = '''create index idx_reader_role on reader_role(reader_name asc , role_name asc )'''
index_borrow_reader_name_book_id = '''create index idx_borrow_reader_name_book_id on borrow(reader_name asc , book_id asc )'''

# 初始化数据
init_data_sql = '''
INSERT INTO admin (account, password) VALUES (N'张嘉旺', N'123456');
INSERT INTO book (book_id, name, author, publisher, publish_date, price, summary, is_borrowed) VALUES (N'1001', N'高等教学', N'李苹', N'人民教不动出版社', N'2010-08-28', 55.3, N'来不及了，学不会了', 1);
INSERT INTO book (book_id, name, author, publisher, publish_date, price, summary, is_borrowed) VALUES (N'1002', N'SqlServer', N'陈涵亮', N'人民教育出版社', N'2022-06-11', 99.9, N'保你期末不挂科', 1);
INSERT INTO book (book_id, name, author, publisher, publish_date, price, summary, is_borrowed) VALUES (N'1003', N'下饭菜谱', N'中华小当家', N'好食道出版社', N'2019-07-24', 89, N'光盘小助手', 1);
INSERT INTO book (book_id, name, author, publisher, publish_date, price, summary, is_borrowed) VALUES (N'1004', N'数据库', N'阿斯蒂芬', N'山东大学出版社', N'2022-06-01', 30.6, N'防止内卷', 0);
INSERT INTO book (book_id, name, author, publisher, publish_date, price, summary, is_borrowed) VALUES (N'1005', N'python', N'Guido', N'我也不知道是哪个出版社', N'2022-06-06', 99.9, N'躺平战士', 0);
INSERT INTO reader (name, gender, unit, tel, balance, password) VALUES (N'董诗斌', 1, N'陕西省西安市蓝田县文化路446号', N'13924665646', 200, N'123456');
INSERT INTO reader (name, gender, unit, tel, balance, password) VALUES (N'思光', 0, N'河北省张家口市涿鹿县太平路最上面的水泥管里', N'17450716839', 47, N'123456');
INSERT INTO reader (name, gender, unit, tel, balance, password) VALUES (N'张嘉旺', 1, N'山东大学威海校区', N'13939334508', 100, N'123456');
INSERT INTO reader (name, gender, unit, tel, balance, password) VALUES (N'张三', 1, N'山东省威海市环翠区文化西路第三个垃圾桶旁', N'374953213', 0, N'123456');
INSERT INTO role (name, borrow_num_limit, borrow_date_limit) VALUES (N'本科生', 5, 60);
INSERT INTO role (name, borrow_num_limit, borrow_date_limit) VALUES (N'教师', 15, 180);
INSERT INTO role (name, borrow_num_limit, borrow_date_limit) VALUES (N'其他', 5, 30);
INSERT INTO role (name, borrow_num_limit, borrow_date_limit) VALUES (N'研究生', 10, 180);
INSERT INTO borrow (reader_name, book_id, borrow_date, return_date, is_overdue, overdue_money, is_process) VALUES (N'张三', N'1001', N'2022-06-08 17:38:57.000', null, null, null, null);
INSERT INTO borrow (reader_name, book_id, borrow_date, return_date, is_overdue, overdue_money, is_process) VALUES (N'董诗斌', N'1002', N'2022-06-08 17:30:27.000', null, null, null, null);
INSERT INTO borrow (reader_name, book_id, borrow_date, return_date, is_overdue, overdue_money, is_process) VALUES (N'张嘉旺', N'1003', N'2022-06-04 02:02:11.000', N'2023-06-04 02:02:16.000', 1, 99, 0);
INSERT INTO reader_role (reader_name, role_name) VALUES (N'董诗斌', N'教师');
INSERT INTO reader_role (reader_name, role_name) VALUES (N'思光', N'本科生');
INSERT INTO reader_role (reader_name, role_name) VALUES (N'张嘉旺', N'本科生');
INSERT INTO reader_role (reader_name, role_name) VALUES (N'张三', N'本科生');
        '''

'''
建立触发器语句
'''

# 创建读者表更新触发器，扣款时检查读者余额是否足够。
tr_reader_id_check_update_delete = ''' create trigger tr_reader_id_check on reader for update as
 begin
     if update(balance) and (select balance from reader where name in (select name from inserted)) < 0
         begin
             raiserror('该读者余额不足', 16, 1);
             rollback tran;
         end
 end'''

# 创建借阅表删除触发器，检查读者或图书是否存在，如果不存在则抛出异常。
tr_borrow_id_check_update_delete = '''create trigger tr_borrow_id_check on borrow for delete as
begin
    if not exists(select * from reader where name in (select reader_name from deleted))
        begin
            raiserror('该借阅记录不存在', 16, 1);
            rollback tran;
        end
    if not exists(select * from book where book_id in (select book_id from deleted))
        begin
            raiserror('该借阅记录不存在', 16, 1);
            rollback tran;
        end
end'''

# 创建借阅表插入、更新触发器，检查读者或图书信息是否存在，如果不存在则抛出异常。
tr_borrow_insert_update = '''create trigger tr_borrow_insert on borrow for insert, update as
begin
    if not exists(select * from reader where name in (select reader_name from inserted))
        begin
            raiserror('该读者不存在', 16, 1);
            rollback tran;
        end
    if not exists(select * from book where book_id in (select book_id from inserted))
        begin
            raiserror('该图书不存在', 16, 1);
            rollback tran;
        end
end'''


# 给字符串两边加上单引号
def add_single_quotation_marks(string):
    return "'" + string + "'"


# 建库建表、初始化数据
def createDB():
    # 开启自动提交
    conn.autocommit(True)
    # 确保数据库文件保存路径存在
    if not os.path.exists(path):
        os.makedirs(path)
    # 建立数据库
    formatePrint("正在创建数据库...")
    create_db_sql = "create database " + db_name + " on primary (name = '" + db_name + "',\
            filename ='" + db_file_path + ".mdf') \
            log on (name = '" + log_name + "',filename = '" + log_file_path + ".ldf') "

    cur.execute(create_db_sql)
    # 切换数据库到新建的数据库
    cur.execute("use " + db_name)
    formatePrint('数据库"' + db_name + '"已创建')
    # 建表
    formatePrint("正在创建数据表...")
    createTable("admin", admin)
    createTable("book", book)
    createTable("reader", reader)
    createTable("role", role)
    createTable("reader_role", reader_role)
    createTable("borrow", borrow)
    formatePrint("数据表创建完成")
    # 创建索引
    createDBIndex()
    # 创建触发器
    formatePrint("正在创建触发器...")
    createTrigger(tr_borrow_id_check_update_delete)
    createTrigger(tr_borrow_insert_update)
    formatePrint("触发器创建完成")
    # 初始化数据
    init_data()
    # 关闭自动提交
    conn.autocommit(False)


# 建表
def createTable(table_name, create_table_sql):
    cur.execute(create_table_sql)


# 初始化数据
def init_data():
    formatePrint("正在初始化数据...")
    cur.execute(init_data_sql)
    formatePrint("初始化数据完成")
    formatePrint("默认管理员账户已创建，账号：张嘉旺，密码：123456")


# 创建索引
def createDBIndex():
    formatePrint("正在创建索引...")
    cur.execute(index_reader_name)
    cur.execute(index_book_id)
    cur.execute(index_role_name)
    cur.execute(index_reader_role)
    cur.execute(index_borrow_reader_name_book_id)
    formatePrint("索引创建完成")


# 创建触发器
def createTrigger(trriger_name):
    cur.execute(trriger_name)


# 打印信息
def formatePrint(msg):
    print("【 " + msg + " 】")


# 关闭数据库
def closeDB():
    cur.close()
    conn.close()
    formatePrint("数据库关闭成功")


# 删除数据库
def dropDB():
    conn.autocommit(True)
    drop_db_sql = "drop database " + db_name
    cur.execute(drop_db_sql)
    formatePrint('数据库"' + db_name + '"已删除')
    conn.autocommit(False)


# 控制台清屏
def clear():
    # for windows
    if name == 'nt':
        system('cls')
    # for mac and linux(here, os.name is 'posix')
    else:
        system('clear')


# 提示用户继续
def goAhead():
    print()
    system('pause')
    clear()


# 用户界面通过输入主机地址、端口号、账号、密码完成数据库连接。
clear()
''' 
0 = 黑色       8 = 灰色
 1 = 蓝色       9 = 淡蓝色
 2 = 绿色       A = 淡绿色
 3 = 浅绿色     B = 淡浅绿色
 4 = 红色       C = 淡红色
 5 = 紫色       D = 淡紫色
 6 = 黄色       E = 淡黄色
 7 = 白色       F = 亮白色
 '''
os.system("color 0A")
while True:
    print('''
   ____   _                        __ _              _     _                                      __ _  
  |_  /  | |_     __ _    _ _     / _` |          _ | |   (_)    __ _   __ __ __ __ _    _ _     / _` | 
   / /   | ' \   / _` |  | ' \    \__, |         | || |   | |   / _` |  \ V  V // _` |  | ' \    \__, | 
  /___|  |_||_|  \__,_|  |_||_|   |___/           \__/   _|_|_  \__,_|   \_/\_/ \__,_|  |_||_|   |___/  
                                                                                        @author: 张嘉旺
    ''')
    print("=======图书管理系统初始化=========")
    host = input("请输入主机地址：")
    port = input("请输入端口号：")
    user = input("请输入数据库登录账号：")
    password = input("请输入数据库登录密码：")
    formatePrint("正在登录...")

    # 连接数据库
    try:
        conn = pymssql.connect(host=host, database='master', user=user, password=password, port=port, charset='utf8')
    except:  # 捕获数据库连接异常
        formatePrint("数据库连接失败，请检查输入信息是否正确")
        goAhead()
        continue
    formatePrint("登录成功")
    goAhead()
    break
# 判断数据库是否已经存在
is_database_exists_sql = "select * from sys.databases where name  = '" + db_name + "'"
cur = conn.cursor()
cur.execute(is_database_exists_sql)
data = cur.fetchall()
if len(data) == 0:
    while True:
        print("\n=======图书管理系统初始化=========")
        ch = input("第一次使用本系统，是否初始化数据库？（Y/N）")
        if ch == 'Y' or ch == 'y':
            formatePrint("正在初始化数据库...")
            createDB()
            formatePrint("初始化数据库成功")
            goAhead()
            break
        elif ch == 'N' or ch == 'n':
            conn.close()
            formatePrint('程序已退出')
            system("pause")
            exit(0)
        else:
            formatePrint("非法字符，请重新输入")
            goAhead()
else:
    while True:
        print("\n=======图书管理系统初始化=========")
        ch = input("数据库已存在，是否重置数据库？（Y/N）")
        if ch == 'Y' or ch == 'y':
            formatePrint("正在重置数据库...")
            try:
                dropDB()
            except OperationalError or MSSQLDatabaseException as e:
                formatePrint("删除数据库失败，请关闭可能占用数据库的进程后重试")
                goAhead()
                continue
            createDB()
            formatePrint("数据库重置成功")
            goAhead()
            break
        elif ch == 'N' or ch == 'n':
            break
        else:
            formatePrint("非法字符，请重新输入")
            goAhead()
